import threading
from typing import List, Tuple

import pymysql
from dbutils.pooled_db import PooledDB

db_config = {
    'local': {
        'host': 'localhost',
        'user': 'root',
        'password': '123456',
        'database': 'chinese_poetry',
        'charset': 'utf8mb4'
    }, 'remote': {
        'host': '',
        'user': 'mengyao-poetry',
        'password': 'mengyao-poetry',
        'database': 'chinese_poetry',
        'port': 3306,  # 默认3306，根据实际情况修改
        'charset': 'utf8mb4'
    }
}


class DBConfig:
    """
    MySQL数据库操作工具类（使用连接池实现连接复用）

    功能：
    - 自动管理数据库连接（连接池）
    - 支持事务操作
    - 提供CRUD基本操作
    - 支持参数化查询防止SQL注入
    """

    def __init__(self, db_type, pool_size=5):
        """
        初始化数据库连接池

        :param db_type: 远程数据库or本地数据库
        :param pool_size: 连接池大小，默认5
        """
        self.pool = PooledDB(
            creator=pymysql,
            maxconnections=pool_size,
            host=db_config[db_type]['host'],
            port=db_config[db_type]['port'],
            user=db_config[db_type]['user'],
            password=db_config[db_type]['password'],
            database=db_config[db_type]['database'],
            charset=db_config[db_type]['charset'],
            autocommit=False,
            cursorclass=pymysql.cursors.DictCursor
        )
        # 线程锁，用于事务操作
        self._lock = threading.Lock()

    def get_connection(self):
        """从连接池获取数据库连接"""
        return self.pool.connection()

    def execute_query(self, sql, params=None):
        """
        执行查询操作（SELECT）

        :param sql: SQL语句
        :param params: 参数列表或字典
        :return: 查询结果列表（字典形式）
        """
        conn = self.get_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute(sql, params)
                result = cursor.fetchall()
                return result
        finally:
            conn.close()

    def execute(self, sql, params=None):
        """
        执行写操作(INSERT/UPDATE/DELETE)
        :param sql: SQL语句
        :param params: 查询参数
        :return: 影响的行数
        """
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                affected_rows = cursor.execute(sql, params or ())
                conn.commit()
                return affected_rows

    def batch_execute(self, sql, params_list: List[Tuple]):
        """
        批量执行相同SQL语句（使用executemany）
        :param sql: SQL语句
        :param params_list: 参数元组列表
        :return: 影响的总行数
        """
        if not params_list:
            return 0

        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                # 使用您期望的格式
                if params_list:
                    affected_rows = cursor.executemany(sql, params_list)
                    conn.commit()
                    return affected_rows
        return 0

    def get_one(self, sql, params=None):
        """
        获取单条记录
        :param sql: SQL语句
        :param params: 查询参数
        :return: 单条结果(字典形式)
        """
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(sql, params or ())
                return cursor.fetchone()

    def transaction(self):
        """
        返回一个事务上下文管理器
        使用示例：
            with db_pool.transaction() as conn:
                conn.execute(...)
                conn.execute(...)
        """
        return TransactionContext(self.pool, self._lock)

    def close(self):
        """关闭连接池"""
        self.pool.close()


class TransactionContext:
    """事务上下文管理器"""

    def __init__(self, pool, lock):
        self.pool = pool
        self.lock = lock
        self.conn = None

    def __enter__(self):
        """进入上下文，获取连接并开始事务"""
        # 使用锁确保事务操作的线程安全
        self.lock.acquire()
        self.conn = self.pool.connection()
        self.conn.begin()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """退出上下文，提交或回滚事务"""
        try:
            if exc_type:
                self.conn.rollback()
            else:
                self.conn.commit()
        finally:
            self.conn.close()
            self.lock.release()

    def execute(self, sql, params=None):
        """在事务中执行SQL"""
        with self.conn.cursor() as cursor:
            return cursor.execute(sql, params or ())

    def batch_execute(self, sql, params_list: List[Tuple]):
        """
        在事务中批量执行SQL
        """
        if not params_list:
            return 0

        with self.conn.cursor() as cursor:
            if params_list:
                return cursor.executemany(sql, params_list)
        return 0

    def query(self, sql, params=None):
        """在事务中执行查询"""
        with self.conn.cursor() as cursor:
            cursor.execute(sql, params or ())
            return cursor.fetchall()

    def get_one(self, sql, params=None):
        """在事务中获取单条记录"""
        with self.conn.cursor() as cursor:
            cursor.execute(sql, params or ())
            return cursor.fetchone()
